/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package FeeType;
import java.sql.*;
import java.util.Vector;

/**
 *
 * @author mishra
 */
public class Query {
    static Connection con=null;
    
     public static int add(FeeTypeBean ftb) throws SQLException
    {
        int i=0,k=0;
        try
        {
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("insert into fee_type values(feetypeid.nextval,?,sysdate)");
            pre.setString(1, ftb.getFeeTypeName());
            k=pre.executeUpdate();
            if(k!=0)
            {
                i=getMaxId();
            }
        }
        catch(Exception e)
        {
            System.out.println(e);
        }
        finally
        {
            con.close();
        }
        return i;
    }
    public static int getMaxId() throws SQLException
    {
        int i=0;
        try
        {
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("select max(fee_type_id) from fee_type");
            ResultSet rs=pre.executeQuery();
            while(rs.next())
            {
                i=rs.getInt(1);
            }
        }
        catch(Exception e)
        {
            System.out.println(e);
        }finally{con.close();}
        return i;
    }
public static FeeTypeBean viewSpecific(String cname, String val) throws SQLException
{
    FeeTypeBean ftb= new FeeTypeBean();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from fee_type where "+cname+"=?");
        pre.setString(1, val);
        ResultSet rs=pre.executeQuery();
        while(rs.next())
        {
            ftb.setFeeTypeId(rs.getInt(1));
            ftb.setFeeTypeName(rs.getString(2));
            ftb.setEntryDate(rs.getDate(4));
        }
        
    }catch(Exception e)
    {
        System.out.println(e);
    }finally{
        con.close();
    }
    return ftb;
}
    
public static Vector viewAll() throws SQLException
{
    Vector<Object> list=new Vector<Object>();
    
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from fee_type order by fee_type_id");
        ResultSet rs=pre.executeQuery();
        while(rs.next())
        {
           FeeTypeBean ftb= new FeeTypeBean();
           ftb.setFeeTypeId(rs.getInt(1));
           ftb.setFeeTypeName(rs.getString(2));
           ftb.setEntryDate(rs.getDate(3));
           list.add(ftb);
        }
        
    }catch(Exception e)
    {
        System.out.println(e);
    }finally{
        con.close();
    }
    return list;
}
public static int edit(FeeTypeBean ftb) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("update FEE_TYPE set FEE_TYPE_NAME=?, entrydate=sysdate where FEE_TYPE_ID=?");
            pre.setString(1, ftb.getFeeTypeName());
            pre.setInt(2, ftb.getFeeTypeId());
            i = pre.executeUpdate();
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }
    public static int delete(int id) throws SQLException
        {
            int i=0;
            try
            {
              con=DBConnection.Connection.getConnection();
              PreparedStatement pre=con.prepareStatement("delete from fee_type where fee_type_id=?");
              pre.setInt(1,id);
              i=pre.executeUpdate();
            }
            catch(Exception e)
            {
               return i; 
            }
            finally
            {
              con.close();  
            }
            return i;
        }  
    public static int getFeeTypeId(String cname, String val) throws SQLException
{   int i=0;
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select FEE_TYPE_ID from fee_type where "+cname+"=?");
        pre.setString(1, val);
        ResultSet rs=pre.executeQuery();
        if(rs.next())
        {
            i=rs.getInt(1);
        }
        
    }catch(Exception e)
    {
        System.out.println(e);
    }finally{
        con.close();
    }
    return i;
}
    
        public static Vector SearchByFeeType(String name) throws SQLException
    {
       Vector<Object> list=new Vector<Object>();
       try{
           con=DBConnection.Connection.getConnection();
           PreparedStatement pre=con.prepareStatement("select * from fee_type where fee_type_name like '"+name+"%'");
           ResultSet rs=pre.executeQuery();
           while(rs.next())
           {
               FeeTypeBean ftb=new FeeTypeBean();

               ftb.setFeeTypeId(rs.getInt(1));
               ftb.setFeeTypeName(rs.getString(2));

               list.add(ftb);
           }
       }
       catch(Exception e){
       return list;
       }finally{con.close();}
       return list;
    }
}
